Skip Headers

Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Part Number B14211-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# What's New in Oracle Performance?

This section describes new performance features of Oracle Database 10g Release 2 (10.2) and provides pointers to additional information. The features and enhancements described in this section comprise the overall effort to optimize server performance.

For a summary of all new features for Oracle Database 10g Release 2 (10.2), see Oracle Database New Features.

The new and updated performance features in Oracle Database 10g Release 2 (10.2) include:

- Active Session History Reports

  Active Session History (ASH) reports contains ASH information that can be used to identify blocker and waiter identities and their associated transaction identifiers and SQL for a specified duration. See "Active Session History Reports".

- Automatic PGA Memory Management

  A new view has been added to monitor dynamic PGA memory usage for each Oracle process. See "Monitoring the Performance of the Automatic PGA Memory Management" for descriptions of the `V$PROCESS_MEMORY` view.

- Automatic Shared Memory Management

  Automatic Shared Memory Management simplifies the configuration of System Global Area (SGA) memory-related parameters through self-tuning algorithms. Automatic Shared Memory Management has been enhanced and the streams pool is now auto tuned as part of the automatic SGA management. See "Automatic Shared Memory Management".

- Automatic Tuning of Multiblock Read Count

  The `DB_FILE_MULTIBLOCK_READ_COUNT` initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly. See "Controlling the Behavior of the Query Optimizer" for more information on the `DB_FILE_MULTIBLOCK_READ_COUNT` parameter.

- Automatic Workload Repository Reports

  Automatic Workload Repository (AWR) reports display statistics for a range of snapshot Ids. Two new reports, `awrsqrpt.sql` and `awrsqrpi.sql`, have been added to view statistics for a particular SQL statement. See "Automatic Workload Repository Reports".

- Configurable Automatic Workload Repository SQL Collection

  The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes, including SQL statements that are producing the highest load on the system. This feature has been enhanced to enable the configuration of the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, and Version Count). See "Automatic Workload Repository".

- Enhanced End to End Application Tracing

  End to End Application Tracing identifies the source of an excessive workload, such as a high load SQL statement. This feature has been enhanced to enable SQL tracing on a session, instance wide, or an entire database level. See "End to End Application Tracing".

- Improved System Statistics

  The `V$SYSSTAT` view has added rows to capture the total number of physical I/O's performed by any Oracle process. See "Examine Load". Additionally, a new event has been added to reduce the memory used for maintaining statistics on events in the Other wait class. See "events in wait class other".

- SQL Access Advisor

  The SQL Access Advisor and its related `DBMS_ADVISOR` package now recommend function-based indexes, the recommendation process can now be interrupted and there are improvements in the Oracle Enterprise Manager. See Chapter 17, "SQL Access Advisor".

- SQL Profiles

  The `DBMS_SQLTUNE` package now also offers the ability to allow SQL with text differing only in its literal values to share a SQL Profile by normalizing literal text values to bind variables. See "Accepting a SQL Profile".

- SQL Tuning Advisor

  It is now possible to monitor the execution progress of the SQL Tuning Advisor using the new `V$ADVISOR_PROGRESS` view. See "Checking the Progress of the SQL Tuning Advisor".

- SQL Tuning Sets

  SQL Tuning Sets can now be exported to or imported from another system using the `DBMS_SQLTUNE` package procedures. See "Transporting a SQL Tuning Set".

- `V$SQLSTATS` View

  A new view, `V$SQLSTATS`, returns performance statistics for SQL cursors. V$SQLSTATS contains a subset of columns that appear in `V$SQL` and `V$SQLAREA`. However, the `V$SQLSTATS` view differs from `V$SQL` and `V$SQLAREA` in that it is faster, more scalable, and has a greater data retention. It is recommended that `V$SQLSTATS` be used in place of `V$SQL` when fetching statistics for SQL cursors.

  See Also: Oracle Database Reference for more information on `V$SQLSTATS`